#!/usr/bin/env python

import operator, os, sys, cherrypy, template, ajax, datetime, random
import sqlite3
from formencode import Invalid
from genshi.input import HTML
from genshi.filters import HTMLFormFiller, HTMLSanitizer
from model import *

class Root(object):

	def __init__(self, db_file ):
		self.db_file = db_file 

#Index
	@cherrypy.expose
	@template.output('index.html') 
	def index(self):		
		db = sqlite3.connect(self.db_file ,isolation_level=None)
		sql = db.cursor()
		
		events = []
		sql.execute('select * from events')

		for row in sql:
			event = Event(row[0],row[1],row[2],row[3],row[4])
			events.append(event)

		for event in events:
			event.poll_title = sql.execute('SELECT TITLE FROM POLLS WHERE ID=?', [event.poll_id]).fetchone()[0]
			event.tag_title = sql.execute('SELECT TITLE FROM tag_descriptions WHERE ID=?', [event.tag_id]).fetchone()[0]

		tags = []
		sql.execute('select * from tag_descriptions')
		for row in sql:
			tag = Tag(row[0],row[1],row[2])
			tags.append(tag)
		for tag in tags:
			sql.execute('SELECT COUNT(1), POLLS.STATE FROM POLLS, TAGS WHERE TAGS.POLL_ID = POLLS.ID AND TAGS.ID =? GROUP BY POLLS.STATE;', [tag.id])
			tag.state=[0,0,0]
			for row in sql:
				tag.state[int(row[1])+1]=row[0]

		sql.execute('select * from polls')
		polls = []
		for row in sql:
			poll = Poll(row[0],row[1],row[2],row[3], row[4], row[5])
			polls.append(poll)
		sql.close
		return template.render(tags=tags, polls=polls, events=events)
	
	
	
	
#Event Handlers
	@cherrypy.expose
	@template.output('tag_detail.html')
	def event_edit(self, id, pause=False,stop=False,delete=False, **data):
		db = sqlite3.connect(self.db_file ,isolation_level=None)
		sql = db.cursor()
		print cherrypy.request.path_info
		print data['CURRENT_URL']
		if cherrypy.request.method == 'POST':
			tag_id = sql.execute('SELECT TAG_ID FROM events WHERE ID=?', [id]).fetchone()[0]
			if pause:
				sql.execute('UPDATE events SET STATUS="PAUSED" WHERE ID=?', [id])
				raise cherrypy.HTTPRedirect(data['CURRENT_URL'])
			elif stop:
				sql.execute('UPDATE events SET STATUS="STOPPED" WHERE ID=?', [id])
				raise cherrypy.HTTPRedirect(data['CURRENT_URL'])
			elif delete:
				sql.execute('DELETE FROM events WHERE ID=?', [id])
				raise cherrypy.HTTPRedirect(data['CURRENT_URL'])


	
#Tag Handlers
	@cherrypy.expose
	@template.output('tag_detail.html')
	def tag(self, id):
	
		db = sqlite3.connect(self.db_file ,isolation_level=None)
		sql = db.cursor()
		sql.execute('SELECT ID, TITLE, DESCRIPTION FROM tag_descriptions WHERE ID =?', [id])
		row = sql.fetchone()
		if row: 
			tag = Tag(row[0],row[1],row[2])
			tag.polls = []
		else:
			raise cherrypy.NotFound()
		
		sql.execute('SELECT POLLS.ID, POLLS.TITLE, POLLS.MINUTES, POLLS.SENSOR, POLLS.ARGUMENTS, POLLS.STATE FROM POLLS, TAGS WHERE TAGS.POLL_ID = POLLS.ID AND TAGS.ID =?', [id])
		for row in sql:
			tag.polls.append(Poll(row[0], row[1], row[2], row[3], row[4], row[5]))
		
		tag.events = []
		sql.execute('select * from events where TAG_ID=?',[tag.id])
		
		for row in sql:
			event = Event(row[0],row[1],row[2],row[3],row[4])
			tag.events.append(event)
			
		for event in tag.events:
			event.poll_title = sql.execute('SELECT TITLE FROM POLLS WHERE ID=?', [event.poll_id]).fetchone()[0]
			event.tag_title = sql.execute('SELECT TITLE FROM tag_descriptions WHERE ID=?', [event.tag_id]).fetchone()[0]

		sql.close
		return template.render(tag=tag)
		

	@cherrypy.expose
	@template.output('tag_edit.html')
	def tag_edit(self, id, cancel=False, **data):
		
		db = sqlite3.connect(self.db_file ,isolation_level=None)
		sql = db.cursor()
		result = sql.execute('SELECT ID, TITLE, DESCRIPTION FROM tag_descriptions WHERE ID =?', [id]).fetchone()

		if result:
			tag = Tag(result[0],result[1],result[2])
		else:
			raise cherrypy.NotFound()
		sql.close 
		 
		
		if cherrypy.request.method == 'POST':
			if cancel:
				raise cherrypy.HTTPRedirect('/tag/%s/' % tag.id)
		
			try:
				data = TagForm().to_python(data)
				data['title'] = (HTML(data['title']) | HTMLSanitizer()).render('xhtml')
				
				db = sqlite3.connect(self.db_file ,isolation_level=None)
				sql = db.cursor()
				sql.execute('UPDATE tag_descriptions SET TITLE=?, DESCRIPTION=? WHERE ID=?', (data['title'],data['description'],tag.id))
				
				sql.close
		
				if not ajax.is_xhr():
					raise cherrypy.HTTPRedirect('/tag/%s/' % tag.id)
				return template.render('tag_form.html', tag=tag)
			except Invalid, e:
				errors = e.unpack_errors()
		else:
			errors = {}

		if ajax.is_xhr():
			stream = template.render('tag_form.html', errors=errors)
		else:
			stream = template.render( tag=tag, errors=errors)
		return stream | HTMLFormFiller(data=data)


	
	
	@cherrypy.expose
	@template.output('tag_edit.html')
	def tag_add(self, id, cancel=False, **data):
		
		if cherrypy.request.method == 'POST':
			if cancel:
				raise cherrypy.HTTPRedirect('/')
			try:
				data = TagForm().to_python(data)
				data['title'] = (HTML(data['title']) | HTMLSanitizer()).render('xhtml')
				tag = Tag(data['title']) 

				db = sqlite3.connect(self.db_file ,isolation_level=None)
				sql = db.cursor()
				sql.execute('SELECT ID FROM tag_descriptions WHERE TITLE =?', [tag.title])
				row = sql.fetchone()
				tag.state=[0,0,0]
				
				if row:
					tag.id = row[0]
				else:
					sql.execute('insert into tag_descriptions (ID, TITLE ) values ( ?, ?)', ( tag.id, tag.title ))

				sql.execute('insert into tags (ID, POLL_ID ) values ( ?, ?)', ( tag.id, id ))
								
				sql.execute('SELECT COUNT(1), POLLS.STATE FROM POLLS, TAGS WHERE TAGS.POLL_ID = POLLS.ID AND TAGS.ID =? GROUP BY POLLS.STATE;', [tag.id])
				
				for row in sql:
					tag.state[int(row[1])+1]=row[0]

				sql.close
				
				if not ajax.is_xhr():
					raise cherrypy.HTTPRedirect('/')
				return template.render('tag_summary.html', tag=tag)
			except Invalid, e:
				errors = e.unpack_errors()
		else:
			errors = {}

		if ajax.is_xhr():
			stream = template.render('tag_form.html', tag=None, errors=errors)
		else:
			stream = template.render( tag=None, errors=errors)
		return stream | HTMLFormFiller(data=data)

	@cherrypy.expose
	@template.output('tag_remove.html')
	def tag_remove(self, id, cancel=False, **data):
		
		db = sqlite3.connect(self.db_file ,isolation_level=None)
		sql = db.cursor()
		sql.execute('SELECT ID, TITLE, MINUTES, SENSOR, ARGUMENTS, STATE FROM polls WHERE ID =?', [id])
		row = sql.fetchone()
		if row:
			poll = Poll(row[0],row[1],row[2],row[3], row[4], row[5])
		else:
			raise cherrypy.NotFound()
		
		sql.execute('SELECT tag_descriptions.ID, tag_descriptions.TITLE, tag_descriptions.DESCRIPTION FROM tag_descriptions, tags WHERE tags.id = tag_descriptions.id and tags.poll_id=?', [id])
		
		poll.tags = []
		for row in sql:
			tag = Tag(row[0],row[1],row[2])
			poll.tags.append(tag)

		sql.close 

		
		if not poll:
			raise cherrypy.NotFound()
		
		if cherrypy.request.method == 'POST':
			if cancel:
				raise cherrypy.HTTPRedirect('/poll/%s' % id)
			
			db = sqlite3.connect(self.db_file ,isolation_level=None)
			sql = db.cursor()
			
			for tag_id in data:
				sql.execute('DELETE FROM tags WHERE ID =? AND POLL_ID=?', (tag_id,id))
			sql.execute('DELETE FROM tag_descriptions where not exists (select ID FROM tags WHERE tags.id = tag_descriptions.id )')
			sql.execute('DELETE FROM events where not exists (select ID FROM tags WHERE tags.id = events.tag_id )')
			sql.execute('DELETE FROM triggers where not exists (select ID FROM tags WHERE tags.id = triggers.tag_id )')
			sql.execute('DELETE FROM trigger_schedule where not exists (select ID FROM events WHERE events.id = trigger_schedule.event_id )')
			sql.close
			
			
			raise cherrypy.HTTPRedirect('/poll/%s' % id)
			
		else:
			errors = {}

		if ajax.is_xhr():
			stream = template.render('tag_form.html', errors=errors)
		else:
			stream = template.render( poll=poll, errors=errors)
		return stream | HTMLFormFiller(data=data)

	
#Trigger Handlers
	@cherrypy.expose
	@template.output('trigger_summary.html')
	def trigger(self, id, cancel=False, **data):

		db = sqlite3.connect(self.db_file ,isolation_level=None)
		sql = db.cursor()
		result = sql.execute('SELECT ID, TITLE, DESCRIPTION FROM tag_descriptions WHERE ID =?', [id]).fetchone()
		if result:
			tag = Tag(result[0],result[1],result[2])
		else:
			raise cherrypy.NotFound()
		
		tag.triggers = []

		sql.execute('SELECT ID, TAG_ID, DESCRIPTION, ACTION, ARGUMENTS, TIMING FROM triggers WHERE TAG_ID=? ORDER BY timing asc', [id])
		for row in sql:
			trigger = Trigger(row[0], row[1], row[2], row[3], row[4], row[5])
			tag.triggers.append(trigger)
		sql.close 
		return template.render(tag=tag)


	
	def trigger_delete(self,id):		
		
		db = sqlite3.connect(self.db_file ,isolation_level=None)
		sql = db.cursor()
		sql.execute('DELETE FROM TRIGGERS WHERE ID =?', [id])
		sql.execute('DELETE FROM TRIGGER_SCHEDULE WHERE ID =?', [id])
		sql.close		
		raise cherrypy.HTTPRedirect('/')

	@cherrypy.expose
	@template.output('trigger_edit.html')
	def trigger_add(self, id, cancel=False, **data):
		db = sqlite3.connect(self.db_file ,isolation_level=None)
		sql = db.cursor()
		sql.execute('SELECT ID, TITLE, DESCRIPTION FROM tag_descriptions WHERE ID =?', [id])
		result = sql.fetchone()
		if result:
			tag = Tag(result[0],result[1],result[2])
		else:
			raise cherrypy.NotFound()
		
		trigger = Trigger("",id,"","","","")
		if cherrypy.request.method == 'POST':
			if cancel:
				raise cherrypy.HTTPRedirect('/trigger/%s/'%id)
			form = TriggerForm()
			try:
				data = form.to_python(data)
				markup = HTML(data['description']) | HTMLSanitizer()
				data['description'] = markup.render('xhtml')
				
				#(ID text, TAG_ID text, DESCRIPTION text, ACTION text, ARGUMENTS text, TIMING real)")	
				trigger = Trigger(id,data['description'], data['action'], data['arguments'], data['timing'])
				
				db = sqlite3.connect(self.db_file ,isolation_level=None)
				sql = db.cursor()
				sql.execute('INSERT INTO triggers(ID, TAG_ID, DESCRIPTION, ACTION, ARGUMENTS, TIMING ) VALUES (?, ?, ?, ?, ?, ?)', (trigger.id, trigger.tag_id, trigger.description, trigger.action, trigger.arguments, trigger.timing))
				sql.close
				
				raise cherrypy.HTTPRedirect('/trigger/%s/'%id)
				
			except Invalid, e:
				errors = e.unpack_errors()
				print errors
				
		else:
			errors = {}
			
		
		if ajax.is_xhr():
			stream = template.render('trigger_edit.html', errors=errors)
		else:
			stream = template.render( trigger=trigger, tag=tag, new=True, errors=errors)
		return stream | HTMLFormFiller(data=data)

			
	@cherrypy.expose
	@template.output('trigger_edit.html')
	def trigger_edit(self, id, cancel=False, **data):
		
		db = sqlite3.connect(self.db_file ,isolation_level=None)
		sql = db.cursor()
		sql.execute('SELECT ID, TAG_ID, DESCRIPTION, ACTION, ARGUMENTS, TIMING FROM triggers WHERE ID =?', [id])
		row = sql.fetchone()
		if row:
			trigger = Trigger(row[0],row[1],row[2],row[3], row[4], row[5])
		else:
			raise cherrypy.NotFound()
		sql.execute('SELECT ID, TITLE, DESCRIPTION FROM tag_descriptions WHERE ID =?', [trigger.tag_id])
		result = sql.fetchone()
		if result:
			tag = Tag(result[0],result[1],result[2])
		else:
			raise cherrypy.NotFound()

		sql.close 
	
		if cherrypy.request.method == 'POST':
			if cancel:
				raise cherrypy.HTTPRedirect('/trigger/%s/' % tag.id) 
			form = TriggerForm()
			try:
				
				data = form.to_python(data)
				data['description'] = (HTML(data['description']) | HTMLSanitizer()).render('xhtml')
				trigger = Trigger(id, data['tag_id'], data['description'], data['action'], data['arguments'], data['timing'])
					
				db = sqlite3.connect(self.db_file ,isolation_level=None)
				sql = db.cursor()
				sql.execute('UPDATE triggers SET DESCRIPTION=?, ACTION=?, ARGUMENTS=?, TIMING=? WHERE ID=?', (trigger.description, trigger.action, trigger.arguments, trigger.timing, trigger.id))

				sql.close
			
				raise cherrypy.HTTPRedirect('/trigger/%s/' % tag.id)
				
			except Invalid, e:
				errors = e.unpack_errors()
		else:
			errors = {}

		if ajax.is_xhr():
			stream = template.render('trigger_edit.html', errors=errors)
		else:
			stream = template.render( trigger=trigger, tag=tag, new=False, errors=errors)
		return stream | HTMLFormFiller(data=data)


		
#Poll Handlers
	@cherrypy.expose
	@template.output('poll_detail.html')
	def poll(self, id, cancel=False, **data):
		
		db = sqlite3.connect(self.db_file ,isolation_level=None)
		sql = db.cursor()
		sql.execute('SELECT ID, TITLE, MINUTES, SENSOR, ARGUMENTS, STATE FROM polls WHERE ID =?', [id])
		row = sql.fetchone()
		if row:
			poll = Poll(row[0],row[1],row[2],row[3], row[4], row[5])
		else:
			raise cherrypy.NotFound()
		
		rrdb = []
		sql.execute('SELECT TIME, STATE, VALUE, DESCRIPTION FROM rrdb WHERE POLL_ID =? ORDER BY TIME DESC', [id])
		for row in sql:
			rrdb.append((row[0],row[1],row[2],row[3], datetime.datetime.utcfromtimestamp(row[0])))
		
		sql.execute('SELECT tag_descriptions.ID, tag_descriptions.TITLE, tag_descriptions.DESCRIPTION FROM tag_descriptions, tags WHERE tags.id = tag_descriptions.id and tags.poll_id=?', [id])
		poll.tags = []
		for row in sql:
			tag = Tag(row[0],row[1],row[2])
			poll.tags.append(tag)
		
		for tag in poll.tags:
			sql.execute('SELECT COUNT(1), POLLS.STATE FROM POLLS, TAGS WHERE TAGS.POLL_ID = POLLS.ID AND TAGS.ID =? GROUP BY POLLS.STATE;', [tag.id])
			tag.state=[0,0,0]
			for row in sql:
				tag.state[int(row[1])+1]=row[0]

		poll.events = []
		sql.execute('select * from events where POLL_ID=?',[poll.id])
		
		for row in sql:
			event = Event(row[0],row[1],row[2],row[3],row[4])
			poll.events.append(event)
			
		for event in poll.events:
			event.poll_title = sql.execute('SELECT TITLE FROM POLLS WHERE ID=?', [event.poll_id]).fetchone()[0]
			event.tag_title = sql.execute('SELECT TITLE FROM tag_descriptions WHERE ID=?', [event.tag_id]).fetchone()[0]

		sql.close
	
		if ajax.is_xhr(): 
			stream = template.render('poll_form.html', poll=poll, errors=errors, rrdb=rrdb)
		else:
			stream = template.render(poll=poll,  rrdb=rrdb)
		return stream | HTMLFormFiller(data=data)


	@cherrypy.expose
	@template.output('index.html')
	def poll_delete(self,id):		
		
		db = sqlite3.connect(self.db_file ,isolation_level=None)
		sql = db.cursor()
		sql.execute('DELETE FROM POLLS WHERE ID =?', [id])
		sql.execute('DELETE FROM tags WHERE POLL_ID=?', [id])
		sql.execute('DELETE FROM tag_descriptions where not exists (select ID FROM tags WHERE tags.id = tag_descriptions.id )')
		sql.execute('DELETE FROM events where POLL_ID=?', [id])
		sql.execute('DELETE FROM triggers where not exists (select ID FROM tags WHERE tags.id = triggers.tag_id )')
		sql.execute('DELETE FROM trigger_schedule where not exists (select ID FROM events WHERE events.id = trigger_schedule.event_id )')
		
		sql.close
		
		raise cherrypy.HTTPRedirect('/')
	

	@cherrypy.expose
	@template.output('poll_edit.html')
	def poll_add(self, cancel=False, **data):
	
		if cherrypy.request.method == 'POST':
			if cancel:
				raise cherrypy.HTTPRedirect('/')
			form = PollForm()
			try:
				data = form.to_python(data)
				markup = HTML(data['sensor']) | HTMLSanitizer()
				data['sensor'] = markup.render('xhtml')
				poll = Poll(data['title'], data['minutes'], data['sensor'], data['arguments'], -1)
				
				db = sqlite3.connect(self.db_file ,isolation_level=None)
				sql = db.cursor()
				sql.execute('INSERT INTO polls(ID, TITLE, MINUTES, SENSOR, ARGUMENTS, STATE ) VALUES (?, ?, ?, ?, ?, ?)', (poll.id, poll.title, poll.minutes, poll.sensor, poll.arguments, poll.state))
				sql.execute('DELETE FROM poll_schedule WHERE POLL_ID=?',[poll.id])
				sql.execute('INSERT INTO poll_schedule (POLL_ID, TIME) VALUES (?, ?)', (poll.id,datetime.datetime.now() ))
				
				sql.close
				
				if not ajax.is_xhr():
					raise cherrypy.HTTPRedirect('/')
				return template.render('poll_summary.html', poll=poll,
									   num=len(tag.polls))
			except Invalid, e:
				errors = e.unpack_errors()
		else:
			errors = {}

		if ajax.is_xhr():
			stream = template.render('poll_form.html', errors=errors)
		else:
			stream = template.render( poll=None, errors=errors)
		return stream | HTMLFormFiller(data=data)

	@cherrypy.expose
	@template.output('poll_edit.html')
	def poll_edit(self, id, cancel=False, **data):
		
		db = sqlite3.connect(self.db_file ,isolation_level=None)
		sql = db.cursor()
		sql.execute('SELECT ID, TITLE, MINUTES, SENSOR, ARGUMENTS, STATE FROM polls WHERE ID =?', [id])
		row = sql.fetchone()
		if row:
			poll = Poll(row[0],row[1],row[2],row[3], row[4], row[5])
		else:
			raise cherrypy.NotFound()
		sql.close 
	
		if cherrypy.request.method == 'POST':
			if cancel:
				raise cherrypy.HTTPRedirect('/poll/%s/' % id) 
			form = PollForm()
			try:
				data = form.to_python(data)
				print data['arguments']
				poll = Poll(id, data['title'], data['minutes'], data['sensor'], data['arguments'], -1)
				
				markup = HTML(data['sensor']) | HTMLSanitizer()
				data['sensor'] = markup.render('xhtml')
				
				
				db = sqlite3.connect(self.db_file ,isolation_level=None)
				sql = db.cursor()
				print poll.arguments
				sql.execute('UPDATE polls SET TITLE=?, MINUTES=?, SENSOR=?, ARGUMENTS=? WHERE ID=?', (poll.title, poll.minutes, poll.sensor, poll.arguments, poll.id))
				sql.execute('DELETE FROM poll_schedule WHERE POLL_ID=?',[poll.id])
				sql.execute('INSERT INTO poll_schedule (POLL_ID, TIME) VALUES (?, ?)', (poll.id,datetime.datetime.now() ))				
				
				sql.close
				
				if not ajax.is_xhr():
					raise cherrypy.HTTPRedirect('/poll/%s/' % id)
				return template.render('poll_summary.html', poll=poll,
									   num=len(tag.polls))
			except Invalid, e:
				errors = e.unpack_errors()
		else:
			errors = {}

		if ajax.is_xhr():
			stream = template.render('poll_form.html', errors=errors)
		else:
			stream = template.render( poll=poll, errors=errors)
		return stream | HTMLFormFiller(data=data)

		
	@cherrypy.expose
	@template.output('index.xml', method='xml')
#RSS Output
	def feed(self, id=None):
		if id:
			tag = self.data.get(id)
			if not tag:
				raise cherrypy.NotFound()
			return template.render('info.xml', tag=tag)
		else:
			tags = sorted(self.data.values(), key=operator.attrgetter('time'))
			return template.render(tags=tags)

	
def main():
	db_file = 'invigilator.db'
	db = sqlite3.connect(db_file ,isolation_level=None)
   	sql = db.cursor()
	sql.execute("create table IF NOT EXISTS rrdb (TIME TIMESTAMP , POLL_ID text, STATE real, VALUE real, DESCRIPTION text)")
   	sql.execute("create table IF NOT EXISTS polls(ID text, TITLE text, MINUTES real, SENSOR text, ARGUMENTS text, STATE real)")
	sql.execute("create table IF NOT EXISTS poll_schedule(POLL_ID text, TIME TIMESTAMP)")
	sql.execute("create table IF NOT EXISTS tags(ID text, POLL_ID text)")
	sql.execute("create table IF NOT EXISTS tag_descriptions(ID text, TITLE text, DESCRIPTION text)")
	sql.execute("create table IF NOT EXISTS events(ID text, TAG_ID text, POLL_ID text, TIME TIMESTAMP, STATUS text)")
	sql.execute("create table IF NOT EXISTS triggers(ID text, TAG_ID text, DESCRIPTION text, ACTION text, ARGUMENTS text, TIMING real)")	
	sql.execute("create table IF NOT EXISTS trigger_schedule(ID text, EVENT_ID text, TIME TIMESTAMP)")
	sql.close 
	
    # Set up site-wide config first so we get a lo
#	cherrypy.config.update(os.path.abspath(os.path.dirname(__file__) + "/server.config")
#{
#				'tools.encode.on': True, 'tools.encode.encoding': 'utf-8',
#				'tools.decode.on': True,
#				'tools.trailing_slash.on': True,
#				'tools.staticdir.root': os.path.abspath(os.path.dirname(__file__)),
#				})

	cherrypy.quickstart(Root(db_file),"",os.path.abspath(os.path.dirname(__file__)) + "/server.config")

if __name__ == '__main__':
	import formencode
	formencode.api.set_stdtranslation(languages=['en'])
	main()
